﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Maticsoft.DBUtility;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace RLZY.others
{
    class GJJ
    {
        /// <summary>
        /// 公积金账号存在
        /// </summary>
        public DataSet GetListSumGJJ(string 公积金号)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append("  sum(公积金入帐) as 公积金入帐     FROM GJJ_RZ   where    公积金号=@公积金号 ");
            //strSql.Append(" and  是否保护='否' and id<>@id ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@公积金号",公积金号),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 公积金取出
        /// </summary>
        public DataSet GetListSumGJJ_Out(string 公积金号)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append("   sum(取出公积金) as 取出公积金     FROM GJJ_Out   where    公积金号=@公积金号 ");
            //strSql.Append(" and  是否保护='否' and id<>@id ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@公积金号",公积金号),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }

        /// <summary>
        /// Last_GJJ
        /// </summary>
        public DataSet GetLast_GJJ(string 公积金号)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append("  top 1 * from GJJ_RZ  where 公积金号=@公积金号 ");
            strSql.Append(" order by 日期 desc ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@公积金号",公积金号),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 公积金收发存
        /// </summary>
        public int GJJOpration()
        {
            List<String> list = new List<string>();
            list.Add(" if  exists (select name from sysobjects where name='GJJ1')  drop table GJJ1  ");
            list.Add(" select 公积金号,姓名,sum(公积金入帐) as 公积金入帐   into GJJ1 from GJJ_RZ    group by  公积金号,姓名  ");
            list.Add(" if  exists (select name from sysobjects where name='Out1')  drop table Out1  ");
            list.Add(" select 公积金号,sum(取出公积金) as 取出公积金  into Out1 from GJJ_Out   group by  公积金号 ");
            list.Add(" if  exists (select name from sysobjects where name='GJJ_Pay1') drop table GJJ_Pay1 ");
            list.Add(" select  a.公积金号,a.姓名,a.公积金入帐, b.取出公积金 into GJJ_Pay1 from  GJJ1  as a  left join  Out1 as b  on a.公积金号= b.公积金号  ");
            list.Add(" alter table GJJ_Pay1 add  余额 decimal(18,2)  ");
            list.Add(" update GJJ_Pay1  set  余额=公积金入帐-取出公积金 ");
            
            return DbHelperSQL.ExecuteSqlTran(list);
        }
        /// <summary>
        /// Last_GJJ
        /// </summary>
        public DataSet Get_GJJ()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   公积金号,姓名,公积金入帐,取出公积金,余额  from GJJ_Pay1  order by 姓名  asc ");
            
            
            return DbHelperSQL.Query(strSql.ToString(), null);

        }
    }
}
